package jude.java.money.migrate;

import java.io.File;
import java.io.FileOutputStream;
import java.io.FilenameFilter;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.Writer;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import jude.java.money.util.MysqlConnection;
import jude.java.util.DateUtils;
import jude.java.util.FileUtils;
import jude.java.util.Utils;

import org.apache.commons.lang.StringUtils;

/**
 * Zencart 到 Magento 产品数据迁移
 */
public class Zencart3Magento {
	public static final String DEFAULT_OUTPUT_PATH = "E:\\import\\";
	private static final String NEWLINE = "\n";
	private static final String KEY_PREFIX = "${DB_PREFIX}";
	private static final String KEY_LANGUAGE = "${LANGUAGE}";
	private static final String DEFAULT_ATTRIBUTE_SET = "Default";
	private static final String UES_ATTRIBUTE_SET = "UES Default";
//	private static final String IMAGE_GALLERY = "image_gallery";
	private static final int PRODUCT_ATTRIBUTE_COUNT = 8;
	private static final String UNCATEGORIZED_ID = "-1";
	private static final String UNCATEGORIZED_NAME = "Uncategorized";
	private static final String DATE_FORMAT = "yyyy-MM-dd";
	private static final long ONE_DAY = 24 * 3600 * 1000;
	
	public static final String FIELD_SKU = "sku";
	public static final String FIELD_NAME = "name";
	public static final String FIELD_DESCRIPTION = "description";
	/** 查 products_options 表 */
	public static final String FIELD_ATTRIBUTE_SKU = "attribute_sku";
	/** 查 products_options_values 表 */
	public static final String FIELD_ATTRIBUTE_ITEM_SKU = "attribute_item_sku";
	public static final String FIELD_IMAGE = "image";
	public static final String FIELD_URL_KEY = "url_key";
	public static final String FIELD_CATEGORY = "category";
	
	/** magento 中用到的属性id, 在表catalog_product_entity_media_gallery和catalog_product_entity_varchar中用到.
	 * 在1.5.1.0版本中, 两个值分别为74和77
	 * 在1.6 rc2版本中, 两个值分别为77和80
	 * 在1.6.1.0版本中, 后面的值为82
	 * 猜测:
	 * MAGENTO_IMAGE_ATTRIBUTE_ID + 1 为小图
	 * MAGENTO_IMAGE_ATTRIBUTE_ID + 2 为缩略图
	 * MAGENTO_IMAGE_ATTRIBUTE_ID + 3 为gallery
	 * */
	private static final int MAGENTO_IMAGE_ATTRIBUTE_ID = 77;
	private static final int MAGENTO_MEDIA_ATTRIBUTE_ID = 82;
	
	private String outputPath = DEFAULT_OUTPUT_PATH;
	private String zencartDatabase;
	private String zencartPrefix = "";
	private String magentoPrefix = "ues_";
	private MysqlConnection conn; 
	private String migrateName;
	private Writer csvOut;
//	private Writer sqlOut;
	private String now;							//开始导入时间
	
	private List<String> updateSql;				//数据错误, 导致需要更新的sql
	private Set<String> categories;				//此次产品迁移涉及的产品类别ID
	private Set<String> filterCategoryIds;
	private boolean uncategorized;				//是否包含无分类产品
	
	/** zencart的语言id */
	private String languageId = "1";
	private Map<String, String> categoryNames;
	private Map<String, String> categoryParents;
	private Set<String> optionIds;
	private String website = "base";
	
	/** 父类继续子类的产品 */
	private boolean inherit = false;
	/** 导出产品限制 */
	private int limit = 0;
	/** 导出产品限制 */
	private int first = 0;
	/** 可配置产品总开关 */
	private boolean enableConfigurable = false;
	/** 属性集 */
	private String attributeSet = UES_ATTRIBUTE_SET;
	/** 直接指定的可配置属性 */
	private CustomOption configurableAttribute;
	/** 直接指定的自定义选项 */
	private CustomOption[] options;
	/** 忽略所有产品属性 */
	private boolean ignoreAllProductAttributes;
	/** 指定忽略的产品属性名称(title) */
	private String[] ignoredProductAttributes;
	/** 
	 * 部分属性用于可配置产品
	 * 虽然可以设置多个可配置属性, 但一个产品仅支持一个可配置属性
	 * */
	private String[] configurableAttributes = null;
	
	/** 细节图和主图合并用逗号分割保存 */
	private boolean fixedMultiImages = false;
	/** magento 1.5 版本 导入产品时无法正确导入图片bug */
	private boolean fixImageGalleryError = false;
	/** 是否允许多图 */
	private boolean enableMultiImages;
	/** 产品图片目录 */
	private String zencartPath;
	/** 复制图片到指定目录 */
	private String magentoPath;
	/** 单产品最多图片数量 */
	private int maxImages = 12;
	/** 是否忽略产品自带的产品类别  */
	private boolean ignoreProductCategories;
	/** 附加的产品类别名称路径, (如Shoes/UGG/Men), 忽略配置项 {@link #inherit}*/
	private String[] productCategories;
	/** 是否移动图片文件, 否则为复制文件 */
	private boolean moveImageFile = false;
	
	private String galleryPattern = "_\\d+";
	
	private boolean sortAttribute = true;
	
	/** product count */
	private int productCount;
	/** 平均分布新产品 */
	private boolean averageNew;
	/** Set Product as New from Date (格式yyyy-MM-dd HH:mm:ss) */
	private String newFromDate;
	/** Set Product as New to Date (格式yyyy-MM-dd HH:mm:ss) */
	private String newToDate;
	
	private FieldHandlers handlers;
	private String[] extraAttributes;
	private String manufacturer;
	private Set<String> notIncludeCategories;
	private String categoryPrefix = "imported/";
	private Set<String> productNames;		//原始名
	private Set<String> productNames2;		//实际生成名
	
	public Zencart3Magento(String name, String database) throws IOException {
		this.zencartDatabase = database;
		this.migrateName = getMigrateName(name);
		categories = new HashSet<String>();
		updateSql = new ArrayList<String>();
	}
	
	/*
	 * Hibernate映射时可能出错, 则先运行SQL:
	 * update products set products_date_available=null where products_date_available='0000-00-00 00:00:00'
	 * 部分产品的主分类id设置不正确
	 * update products p set master_categories_id=(select categories_id from products_to_categories where products_id=p.products_id limit 1)
	 * where p.master_categories_id=0 or p.master_categories_id is null
	 */
	@SuppressWarnings("unchecked")
	public void migrate() throws IOException {
		new File(outputPath).mkdirs();
		now = DateUtils.toString(new Date(), DateUtils.ALIGN_DATETIME_FORMAT);
		csvOut = new OutputStreamWriter(new FileOutputStream(outputPath + migrateName + ".csv"), "UTF-8");
//		sqlOut = new OutputStreamWriter(new FileOutputStream(outputPath + name + ".sql"), "UTF-8");
		writeSQL("insert into `${DB_PREFIX}catalog_product_entity_media_gallery` (`attribute_id`, `entity_id`, `value`)	select "+MAGENTO_MEDIA_ATTRIBUTE_ID+", `entity_id`, `value` from `${DB_PREFIX}catalog_product_entity_varchar` where `attribute_id`="+MAGENTO_IMAGE_ATTRIBUTE_ID+" and `store_id`=0 and `entity_type_id`=4 and `value`<>'no_selection';\r\n\r\n");
/**
select p.*,d.products_name,d.products_description,cd.categories_name from products p 
	left join products_description d on p.products_id=d.products_id and d.language_id=1 
	left join categories c on  p.master_categories_id=c.categories_id 
	left join categories_description cd on c.categories_id=cd.categories_id and cd.language_id=1
	left join specials s on p.products_id=s.products_id and s.`status`=1
limit 1
 * */
		writeTitle();
		readZencartCategories();
		
		final int total = ((Long)executeSql(true, "select count(*) from `${DB_PREFIX}products`")).intValue();		//hibernate 结果为BigInteger
		productCount = total;
		int start = 0;
		int size = 1000;
		int count = 0;
		int index = 0;
//		int pac = PRODUCT_ATTRIBUTE_COUNT - 1;
		out:
		while(start < total) {
			String sql = 
				"select \r\n"+
				"	p.products_id,p.products_quantity,p.products_model,p.products_image,p.products_price,p.products_date_added,p.products_weight,p.products_status,\r\n" +	//0-6
				"	d.products_name,d.products_description,cd.categories_name,s.specials_new_products_price,\r\n" +		//7-10
				"	c.categories_id,m.manufacturers_name\r\n" +															//11-
				"from `${DB_PREFIX}products` p \r\n"+
				"	left join `${DB_PREFIX}products_description` d on p.products_id=d.products_id and d.language_id=${LANGUAGE} \r\n"+
				"	left join `${DB_PREFIX}categories` c on  p.master_categories_id=c.categories_id \r\n"+
				"	left join `${DB_PREFIX}categories_description` cd on c.categories_id=cd.categories_id and cd.language_id=${LANGUAGE}\r\n"+
				"	left join `${DB_PREFIX}specials` s on p.products_id=s.products_id and s.`status`=1\r\n" +
				"	left join `${DB_PREFIX}manufacturers` m on p.manufacturers_id=m.manufacturers_id\r\n"+
				"limit " + start + "," + size +
				"";
			List<Object[]> o = (List<Object[]>)executeSql(false, sql);
			for (Object[] record : o) {
				if (count >= this.first)
					if (writeProduct(record, null, index++, 0))
						count ++;
				if (limit > 0 && count >= first + limit)
					break out;
			}
			start += size;
		}
		if (notIncludeCategories != null) {
			System.out.println();
			for (String c : notIncludeCategories) {
				System.out.println(c + " not included.");
			}
		}
		System.out.println("total migrate " + count + " products");
		csvOut.close();
//		sqlOut.close();
		
		saveUsage();
		System.out.println();
		System.out.println(migrateName + " Finished");
	}
	
	private void writeSQL(String sql) throws IOException {
//		sql = StringUtils.replace(sql, KEY_PREFIX, magentoPrefix);		//替换前缀
//		sqlOut.write(sql);
	}
	
	private Object executeSql(boolean unique, String sql) {
//		GlobalService service = Services.getGlobalService();
		sql = StringUtils.replace(sql, KEY_PREFIX, zencartPrefix);		//替换前缀
		sql = StringUtils.replace(sql, KEY_LANGUAGE, languageId);
//		System.out.println(sql);
		if (conn == null)
			conn = new MysqlConnection(zencartDatabase);
//		return unique ? service.executeUniqueResultSql(sql) : service.executeSql(sql);
		try {
			return unique ? conn.executeUniqueResultQuery(sql) : conn.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	private void usage(Writer out) throws IOException {
		int n = 1;
		out.write("\n\n\n------------------------------------------------------------------------------|\r\n");
		out.write(n++ + ", 根据 " + (outputPath + migrateName + ".txt") + " 内容创建分类和可配置选项\r\n");
		if (magentoPath == null)
			if (fixImageGalleryError) {
				out.write(n++ + ", 复制图片到 (Magento)/media/catalog/product/ 目录下\r\n");
			} else {
				out.write(n++ + ", 复制图片到 (Magento)/media/import/ 目录下\r\n");
			}
		if (manufacturer != null) {
			out.write(n++ + ", manufacturer属性增加可选值: " + manufacturer + "\r\n");
		}
		out.write(n++ + ", 在Magento后台导入产品: " + (outputPath + migrateName + ".csv\r\n"));
//		out.write(n++ + ", 运行SQL脚本: " + (outputPath + name + ".sql"));
		out.write(n++ + ", 调整产品分类, 设置产品属性,分类,测试\r\n");
		out.write(  "------------------------------------------------------------------------------|\r\n");
	}
	
	private void saveUsage() throws IOException {
		readZencartCategories();
		List<String> cs = new ArrayList<String>();
		cs.addAll(this.categories);
		Set<String> categories = new HashSet<String>();
		categories.addAll(cs);
		for (String c : cs) {
			String p = categoryParents.get(c);
			while (p != null && !p.isEmpty() && !"0".equals(p)) {
				categories.remove(p);
				p = categoryParents.get(p);
			}
		}
		cs.clear();
		cs.addAll(categories);
		Collections.sort(cs);
		Set<String> allCategories = new LinkedHashSet<String>();
		Writer out = new OutputStreamWriter(new FileOutputStream(outputPath + migrateName + ".txt"), "UTF-8");
		int n = 1;
		if (cs.size() > 0) {
			out.write(n++ + ", 分类\r\n");
			out.write(" 创建分类需要使用Magento的WebService, 增加下面的内容到E:\\Workspaces\\php\\phpa\\soap.php并运行后自动创建.\r\n\r\n");
			for (String categoryId : cs) {
				String[] tree = getCategoryTree(categoryId);
				for (int i = 0; i < tree.length; i++) {
					allCategories.add(tree[i]);
				}
			}
			List<String> cas = new ArrayList<String>();
			cas.addAll(allCategories);
			Collections.sort(cas);
			for (String string : cas) {
				out.write("\t\"" + string + "\",\r\n");
			}
			out.write("\r\n\r\n");
		}
		if (productCategories != null && productCategories.length > 0) {
			for (int i = 0; i < productCategories.length; i++) {
				out.write("\t\"" + productCategories[i] + "\",\r\n");
			}
			out.write("\r\n\r\n\r\n");
		}
		
		if (optionIds != null && !optionIds.isEmpty()) {
			out.write(n++ + ", 属性\r\n");
			out.write(" 需要在Magento中创建如下可配置属性, 并增加到属性集: " + attributeSet + "\r\n");
			for (String optionId : optionIds) {
				CustomOption option = readOption(optionId);
				out.write(option.toString() + "\r\n");
			}
			out.write("\r\n\r\n\r\n");
		}
		if (updateSql != null && updateSql.size() > 0) {
			out.write("修复数据SQL(zc)\r\n\r\n");
			for (String sql : updateSql) {
				out.write(sql + "\r\n");
			}
			out.write("\r\n\r\n\r\n");
		}
		
		out.write("常用SQL(mt)\r\n\r\n");
		out.write("1, 复制分类产品\r\n");
		out.write(" insert into `"+magentoPrefix+"catalog_category_product` select 新分类ID, product_id, position from `"+magentoPrefix+"catalog_category_product` where category_id=原分类ID;\r\n\r\n");
		out.write("2, 分类产品转移\r\n");
		out.write(" update `"+magentoPrefix+"catalog_category_product` set category_id=新分类ID where category_id=原分类ID;\r\n\r\n");
		
		
		usage(out);
		out.close();
	}
	
	private String getZenCategoryTree(String categoryId) {
		Map<String, String> names = categoryNames;
		Map<String, String> parents = categoryParents;
		String c = names.get(categoryId);
		String parentId;
		while ((parentId = parents.get(categoryId)) != null && Integer.parseInt(parentId) > 0) {
			String pn = names.get(parentId);
			if (pn == null)
				break;
			c = pn + "/" + c;
			categoryId = parentId;
		}
		return categoryPrefix + c;
	}
	
	private String[] getCategoryTree(String categoryId) {
		Map<String, String> names = categoryNames;
		Map<String, String> parents = categoryParents;
		String[] cc = handleEx(FIELD_CATEGORY, names.get(categoryId), null, null, "0");
		if (cc == null || cc.length == 0)
			return null;
		String c = cc[0];
		if (c != null && c.startsWith("/") && c.length() > 1) {
			cc[0] = c.substring(1);
			return cc;
		}
		String parentId;
		int n = 1;
		while ((parentId = parents.get(categoryId)) != null && Integer.parseInt(parentId) > 0) {
			String pn = handle(FIELD_CATEGORY, names.get(parentId), null, null, String.valueOf(n++));
			if (pn == null)
				break;
			if (pn.startsWith("/")) {
				cc[0] = pn.substring(1) + "/" + c;
				return cc;
			}
			if (pn.length() > 0)
				c = pn + "/" + c;
			categoryId = parentId;
		}
		cc[0] = categoryPrefix + c;
		return cc;
	}
	
/*	private String getCategoryTree(String categoryId) {
		Map<String, String> names = categoryNames;
		Map<String, String> parents = categoryParents;
		String c = handle(FIELD_CATEGORY, names.get(categoryId), null, null, "0");
		if (c != null && c.startsWith("/") && c.length() > 1)
			return c.substring(1);
		String parentId;
		int n = 1;
		while ((parentId = parents.get(categoryId)) != null && Integer.parseInt(parentId) > 0) {
			String pn = handle(FIELD_CATEGORY, names.get(parentId), null, null, String.valueOf(n++));
			if (pn == null)
				break;
			if (pn.startsWith("/") && c.length() > 1)
				return pn.substring(1) + "/" + c;
			c = pn + "/" + c;
			categoryId = parentId;
		}
		return categoryPrefix + c;
	}*/
	
	private boolean hasConfigurable() {
		return enableConfigurable && !DEFAULT_ATTRIBUTE_SET.equals(attributeSet) && 
			(configurableAttributes != null && configurableAttributes.length > 0 || configurableAttribute != null);
	}
	
	private void writeTitle() throws IOException {
		csvOut.write("sku,_store,_attribute_set,_type,_category,_product_websites,color,cost,created_at,custom_design,custom_design_from,custom_design_to,custom_layout_update,description,enable_googlecheckout,gallery,gift_message_available,has_options,image,image_label,is_imported,manufacturer,media_gallery,meta_description,meta_keyword,meta_title,minimal_price,name,news_from_date,news_to_date,options_container,page_layout,price,required_options,short_description,small_image,small_image_label,special_from_date,special_price,special_to_date,status,tax_class_id,thumbnail,thumbnail_label,updated_at,url_key,url_path,visibility,weight,qty,min_qty,use_config_min_qty,is_qty_decimal,backorders,use_config_backorders,min_sale_qty,use_config_min_sale_qty,max_sale_qty,use_config_max_sale_qty,is_in_stock,notify_stock_qty,use_config_notify_stock_qty,manage_stock,use_config_manage_stock,use_config_qty_increments,qty_increments,use_config_enable_qty_increments,enable_qty_increments");
		csvOut.write(",_links_related_sku,_links_related_position,_links_crosssell_sku,_links_crosssell_position,_links_upsell_sku,_links_upsell_position,_associated_sku,_associated_default_qty,_associated_position,_tier_price_website,_tier_price_customer_group,_tier_price_qty,_tier_price_price");
		csvOut.write(",_media_attribute_id,_media_image,_media_lable,_media_position,_media_is_disabled");
		if (hasConfigurable()) {
			if (configurableAttributes == null) {
				csvOut.write("," + attributeCode(configurableAttribute.getTitle()));
			} else {
				for (int i = 0; i < configurableAttributes.length; i++) {
					String attr = attributeCode(configurableAttributes[i]);
					csvOut.write("," + attr);
				}
			}
		}
//		if (enableMultiImages) {
//			csvOut.write("," + IMAGE_GALLERY);
//		}
		csvOut.write(",_custom_option_store,_custom_option_type,_custom_option_title,_custom_option_is_required,_custom_option_price,_custom_option_sku,_custom_option_max_characters,_custom_option_sort_order,_custom_option_row_title,_custom_option_row_price,_custom_option_row_sku,_custom_option_row_sort");
		if (hasConfigurable()) {
			csvOut.write(",_super_products_sku,_super_attribute_code,_super_attribute_option,_super_attribute_price_corr");
		}
		if (extraAttributes != null && extraAttributes.length > 0) {
			for (int i = 0; i < extraAttributes.length; i++) {
				csvOut.write("," + extraAttributes[i]);
			}
		}
		csvOut.write(",ues_unused_field");
		newline();
	}
	
	//	ZENCART
	//	id,type,quantity,model,image,price,price_retail,price_sample,virtual,date_added,
	//	last_modified,date_available,weight,status,tax_class_id,manufacturers_id,ordered,quantity_order_min,quantity_order_units,priced_by_attribute,
	//	is_free,is_call,quantity_mixed,is_always_free_shipping,qty_box_status,quantity_order_max,sort_order,discount_type,discount_type_from,price_sorter,
	//	master_categories_id,mixed_discount_quantity,metatags_title_status,metatags_products_name_status,metatags_model_status,metatags_price_status,metatags_title_tagline_status,start_unit_wholesale,is_wholesale,wholesale_min,
	//	products_name,products_description,categories_name,special_price
	
	//	MAGENTO
	//sku,store,attribute_set,type,category,product_websites,color,cost,created_at,custom_design,
	//custom_design_from,custom_design_to,custom_layout_update,description,enable_googlecheckout,gallery,gift_message_available,has_options,image,image_label,
	//is_imported,manufacturer,media_gallery,meta_description,meta_keyword,meta_title,minimal_price,name,news_from_date,news_to_date,
	//options_container,page_layout,price,required_options,short_description,small_image,small_image_label,special_from_date,special_price,special_to_date,
	//status,tax_class_id,thumbnail,thumbnail_label,updated_at,url_key,url_path,visibility,weight,qty,
	//min_qty,use_config_min_qty,is_qty_decimal,backorders,use_config_backorders,min_sale_qty,use_config_min_sale_qty,max_sale_qty,use_config_max_sale_qty,is_in_stock,notify_stock_qty,use_config_notify_stock_qty,manage_stock,use_config_manage_stock,use_config_qty_increments,qty_increments,use_config_enable_qty_increments,enable_qty_increments,links_related_sku,links_related_position,links_crosssell_sku,links_crosssell_position,links_upsell_sku,links_upsell_position,associated_sku,associated_default_qty,associated_position,tier_price_website,tier_price_customer_group,tier_price_qty,tier_price_price
	/** 
	 * @param pindex 产品次序
	 * @param index 可配置产品的关联产品次序
	 * */
	private boolean writeProduct(Object[] record, CustomOption associated, int pindex, int index) throws IOException {
		String productId = v(record[0]);
		
		if (Boolean.FALSE.equals(record[7])) {
			System.out.println(v(record[2]) + " disabled");
			return false;
		}
		if (Utils.getInteger(record[1], 0) <= 0) {
			System.out.println(v(record[2]) + " sold out");
			return false;
		}
		
		String sku = handle(FIELD_SKU, v(record[2]));
		if (sku == null)
			return false;
		
		int pac = PRODUCT_ATTRIBUTE_COUNT - 1;
		String name = handle(FIELD_NAME, v(record[pac + 1]));
		if (name == null)
			return false;
		
		String categoryId = null;
		List<String> categories = null;
		if (associated == null) {
			if ((!ignoreProductCategories || filterCategoryIds != null || uncategorized)) {
				categoryId = v(record[pac + 5]);
				categories = getCategoryIds(productId);
				for (String c : categories) {
					this.categories.add(c);
				}
				if (categoryId == null && categories.size() > 0)
					categoryId = categories.remove(0);
				else
					categories.remove(categoryId);
			}
			
			if (uncategorized && categoryId == null) {
				categoryId = UNCATEGORIZED_ID;
			} else if (filterCategoryIds != null) {// && filterCategoryIds.size() > 0 不能加, 这样能过滤没有分类的产品
				if (categoryId == null) {
					System.out.println(v(record[2]) + " no category");
					return false;
				}
				boolean b = filterCategoryIds.contains(categoryId);
				if (!b && filterCategoryIds.size() > 0)
					for (String category : categories) {
						if (filterCategoryIds.contains(category)) {
							b = true;
							break;
						}
					}
				if (!b) {
					String tree = getZenCategoryTree(categoryId);
					notIncludeCategories.add(tree);
//					System.out.println(tree + " not included");
					return false;
				}
			}
			if (ignoreProductCategories) {
				if (productCategories == null && productCategories.length == 0) {
					System.out.println(v(record[2]) + " no categories");
					return false;
				}
			}
			if (productCategories != null && productCategories.length > 0) {
				int s = 0;
				if (categoryId == null) {
					categoryId = productCategories[0];
					s = 1;
				}
				if (productCategories.length > s) {
					if (categories == null)
						categories = new ArrayList<String>();
					for (int i = s; i < productCategories.length; i++) {
						categories.add(productCategories[i]);
					}
				}
			}
		}
		
		boolean configurable = false;
		CustomOption configurableAttribute = null;
		List<CustomOption> options = new ArrayList<CustomOption>();
		String image = null;
		String cimage = null;
		int visibility = 4;
		if (associated != null) {
			String s = associated.isSelect() ? associated.getItems()[index].getSku() : associated.getSku();
			s = handle(FIELD_ATTRIBUTE_SKU, s);
			sku = combineSku(sku, s);
			visibility = 1;
		} else {
			configurable = hasConfigurable();
			configurableAttribute = this.configurableAttribute;
			image = v(record[3]);
			if(fixedMultiImages)image = getImages(image)[0];
			if (zencartPath != null && !new File(zencartPath + "/images/" + image).exists()) {
				System.out.println(v(record[2]) + " no image");
				addUpdateSql("update `{$1}products` set products_status=0 where products_model='{$2}';", zencartPrefix, v(record[2]));
				return false;
			}
			cimage = image = image(image);
			if (zencartPath != null)
				cimage = handle(FIELD_IMAGE, image, name, sku, null);
			
			if (this.options != null) {
				for (int i = 0; i < this.options.length; i++) {
					options.add(this.options[i]);
				}
			}
			if (!ignoreAllProductAttributes) {
				CustomOption c = null;
				List<CustomOption> productOptions = getOptions(productId);
				if (productOptions != null) {
					for (int i = productOptions.size() - 1; i >= 0; i--) {
						CustomOption option = productOptions.get(i);
						if (in(option.getTitle(), configurableAttributes)) {
							c = productOptions.remove(i);
							if (configurable && configurableAttribute == null)
								configurableAttribute = c;
						} else {
							if (sortAttribute)
								option.sort();
							options.add(option);
						}
					}
				}
			}
			configurable = configurable && configurableAttribute != null;
		}
		
		boolean hasOption = options.size() > 0;
		String description = v(record[pac + 2]);
		if (StringUtils.isEmpty(description)) {
			description = name;
		}
		description = handle(FIELD_DESCRIPTION, description);
		String manufacturer = this.manufacturer;
		if (manufacturer == null)
			manufacturer = v(record[pac + 6]);
		String name2 = getProductName(name);
		if (!name.equals(name2)) {
			String len = name2.length() > 64 ? "\t--长度超过64, 无法保存" : "";
			addUpdateSql("update `{$1}products_description` set products_name='{$2}' where products_id={$3};{$4}", zencartPrefix, name2, productId, len);
			name = name2;
		}
		String shortDescription = name + "\r\n Model: <i>" + sku + "</i>";
		String[] cTrees = category2(categoryId);
		for (int i = 1; i < cTrees.length; i++) {
			categories.add(cTrees[i]);
		}
		
		//1-30
		writeFields(sku);						//sku
		blankField();							//store
		writeFields(attributeSet);				//attribute_set
		writeFields(type(configurable));		//type (simple/configurable)
		if (associated != null)					//category
			blankField();						//
		else									//
			writeFields(cTrees[0]);				//
		writeFields(website);					//product_websites
		blankField();							//color
		blankField();							//cost
		writeFields(record[5]);					//created_at
		blankFields(4);							//custom_design, custom_design_from, custom_design_to,custom_layout_update
		writeFields(description);				//description
		writeFields(true);						//enable_googlecheckout
		blankField();							//gallery
		writeFields(1);							//gift_message_available(0/1)
		writeFields(hasOption);					//has_options
		writeFields(cimage);					//image (no_selection)
		blankField();							//image_label
		writeFields("Yes");						//is_imported
		writeFields(manufacturer);				//manufacturer
		blankField();							//media_gallery
		blankFields(3);							//meta_description,meta_keyword,meta_title
		blankField();							//minimal_price
		writeFields(name);						//name
		writeProductAsNewDateFields(pindex);	//news_from_date,news_to_date
		//31-40
		writeFields(options_container());		//options_container (Block after Info Column/Product Info Column)	
		blankField();							//page_layout
		writeFields(record[4]);					//price
		writeFields(hasOption);					//required_options
		writeFields(shortDescription);			//short_description
		writeFields(cimage);					//small_image
		blankField();							//small_image_label
		blankField();							//special_from_date
		writeFields(record[pac + 4]);			//special_price
		blankField();							//special_to_date
		//41-50
		writeFields(1);							//status
		writeFields(0);							//tax_class_id
		writeFields(cimage);					//thumbnail
		blankField();							//thumbnail_label
		writeFields(now);						//updated_at
		writeFields(urlkey(sku));				//url_key
		writeFields(urlkey(sku)+".html");		//url_path
		writeFields(visibility);				//visibility
		writeFields(record[6]);					//weight
		writeFields(record[1]);					//qty
		//51-60
		writeFields(0);							//min_qty
		writeFields(1);							//use_config_min_qty
		writeFields(0);							//is_qty_decimal
		writeFields(0);							//backorders
		writeFields(1);							//use_config_backorders
		writeFields(1);							//min_sale_qty
		writeFields(1);							//use_config_min_sale_qty
		writeFields(0);							//max_sale_qty
		writeFields(1);							//use_config_max_sale_qty
		writeFields(1);							//is_in_stock
		//61-70
		blankField();							//notify_stock_qty
		writeFields(1);							//use_config_notify_stock_qty
		writeFields(0);							//manage_stock
		writeFields(1);							//use_config_manage_stock
		writeFields(1);							//use_config_qty_increments
		writeFields(0);							//qty_increments
		writeFields(1);							//use_config_enable_qty_increments
		writeFields(0);							//enable_qty_increments
		blankFields(2);							//links_related_sku,links_related_position
		//71-80
		blankFields(2);							//links_crosssell_sku,links_crosssell_position
		blankFields(4);							//links_upsell_sku,links_upsell_position,associated_sku,associated_default_qty
		blankFields(4);							//associated_position,tier_price_website,tier_price_customer_group,tier_price_qty
		//81
		blankField();							//tier_price_price
		
		if (associated == null) {
			//image media
			writeFields(MAGENTO_MEDIA_ATTRIBUTE_ID, cimage, null, 1, 0);
			
			writeConfigurableFields(null, index);	//配置项的值

			writeCustomOptions(options, 0);
			if (hasConfigurable()) {
				writeConfigurableOptions(sku, configurableAttribute, 0);
			}

			if (handlers != null && extraAttributes != null && extraAttributes.length > 0) {
				for (int i = 0; i < extraAttributes.length; i++) {
					String v = handle(extraAttributes[i], null, name, sku, record);//category(categoryId)
					writeFields(v);
				}
			}
		} else {
			blankFields(5);
			writeConfigurableFields(associated, index);	//配置项的值
			writeCustomOptions(null, 0);
			if (hasConfigurable()) {
				writeConfigurableOptions(sku, null, 0);
			}
			if (handlers != null && extraAttributes != null && extraAttributes.length > 0) {
				for (int i = 0; i < extraAttributes.length; i++) {
					blankField();
				}
			}
		}

		//下面写产品的相关属性(多分类, 自定义属性, 可配置属性产品等)
		newline();
		if (associated == null) {
			List<String> images = getImageGallery2(productId, name, sku, fixedMultiImages ? v(record[3]) : image);
			writeRecords(categories, options, configurableAttribute, sku, images);
			if (configurable) {
				int size = configurableAttribute.isSelect() ? Utils.size(configurableAttribute.getItems()) : 1;
				for (int i = 0; i<size; i++) {
					writeProduct(record, configurableAttribute, pindex, i);
				}
			}
			if (configurable) {
				if (optionIds == null)
					optionIds = new HashSet<String>();
				readProductOptionIds(productId);
			}
			copyImageFile(productId, image, cimage);
		}
/*		writeOptions(options, 0);
		if (configurable) {
			if (optionIds == null)
				optionIds = new HashSet<String>();
			readProductOptionIds(productId);
		} else {
			CustomOption[] options = getOptions(productId);
			writeOptions(options, this.options == null ? 0 : this.options.length);
		}
*/
		return true;
	}
	
//	private int 
	private void writeProductAsNewDateFields(int index) throws IOException {
		if (!this.averageNew) {
			writeFields(newFromDate, newToDate);
			return;
		}
		if (newToDate == null || index % 2 == 0) {	//一半产品做为新产品
			blankFields(2);
			return;
		}
		Date from = new Date(System.currentTimeMillis() - 5 * ONE_DAY);
		long d1 = from.getTime();
		long d2 = DateUtils.toDate(newToDate, DATE_FORMAT).getTime();
		if (d1 > d2) {
			throw new RuntimeException("newFromDate > newToDate");
		}
		int x = 15;									//一天显示x个产品
		int t = (int)((d2 - d1 - 1)/ONE_DAY) + 1;	//总时间长 (天)
		float c = (float)productCount	 / t;		//一天有安排c个新产品	
		int n = Math.max((int)(x / c), 1);			//一个产品显示n天
		int i = (int)(index / c);					//此产品排在第i天的新产品
		d1 += ONE_DAY * i;
		d2 = d1 + n * ONE_DAY;
		String o1 = DateUtils.toString(new Date(d1), DATE_FORMAT);
		String o2 = DateUtils.toString(new Date(d2), DATE_FORMAT);
//		Date date = new Date(d1 + (long)ONE_DAY * Utils.random(t));
//		String to = DateUtils.toString(date, DATE_FORMAT);
		writeFields(o1, o2);
//		System.out.println(o1 + ", " + o2);
	}
	
	private void addUpdateSql(String sql, String... params) {
		sql = StringUtils.replace(sql, KEY_PREFIX, zencartPrefix);
		if (params != null && params.length > 0) {
			for (int i = 0; i < params.length; i++) {
				sql = StringUtils.replace(sql, "{$" + (i + 1) + "}", params[i]);
			}
		}
		if (!sql.endsWith(";"))
			sql += ";";
		updateSql.add(sql);
	}
	
	private String getProductName(String name) {
		if (productNames == null) {
			productNames = new HashSet<String>();
		}
		if (productNames.contains(name)) {
			if (productNames2 == null) {
				productNames2 = new HashSet<String>();
			}
			int n = 2;
			String real = null;
			while (true) {
				real = name + " - M" + StringUtils.leftPad(String.valueOf(n++), 2, '0');
				if (productNames2.contains(real)) {
					continue;
				}
				productNames2.add(real);
				break;
			}
			System.out.println(real);
			return real;
		}
		productNames.add(name);
		return name;
	}
	
	private List<String> getImageGallery2(String productId, String productName, String sku, String image) throws IOException {
		List<String> images = new ArrayList<String>();
		if(fixedMultiImages) {
			String[] is = getImages(image);
			int n = 0;
			for (int i = 1; i < is.length; i++) {
				String p = is[i];
				if (maxImages > 0 && n >= maxImages)
					break;
				if(!new File(zencartPath + "/images/" + p).isFile())
					continue;
				String cp = handle(FIELD_IMAGE, p, productName, sku, String.valueOf(images.size() + 1));
				images.add(cp);
				writeSQL("insert into `${DB_PREFIX}catalog_product_entity_media_gallery` (`attribute_id`, `entity_id`, `value`) select "+MAGENTO_MEDIA_ATTRIBUTE_ID+", entity_id, '" + p + "' from `${DB_PREFIX}catalog_product_entity` where sku='" + sku + "';\r\n");
				copyImageFile(productId, p, cp);
				n++;
			}
			if (is.length > 1)
				writeSQL("\r\n");
			return images;
		}
		if (image == null || !enableMultiImages || zencartPath == null)
			return images;
		File f = new File(zencartPath + "/images/" + image);
		if (!f.exists())
			return images;
		String fileName = f.getName();
		int dot = fileName.lastIndexOf('.');
		if (dot <= 0)
			return images;
		final String name = fileName.substring(0, dot).toLowerCase();
		final String ext = fileName.substring(dot).toLowerCase();
		if (ext.length() == 0)
			return images;
		final int len = fileName.length();
		String[] files = f.getParentFile().list(new FilenameFilter() {
			@Override
			public boolean accept(File dir, String fname) {
				if (fname.length() <= len)
					return false;
				String n = fname.toLowerCase();
				if (!(n.startsWith(name) && n.endsWith(ext)))
					return false;
				if (galleryPattern == null)
					return true;
				String subfix = n.substring(name.length(), n.length()-ext.length());
				return subfix.matches(galleryPattern);
			}
		});
		if (files != null && files.length > 0) {
			String path = image.substring(0, image.length() - fileName.length());
//			StringBuilder sb = new StringBuilder();
			for (int i = 0; i < files.length; i++) {
//				if (i > 0)
//					sb.append(";");
				if (maxImages > 0 && i >= maxImages)
					break;
				String p = path + files[i];
				String cp = handle(FIELD_IMAGE, p, productName, sku, String.valueOf(images.size() + 1));
				images.add(cp);
				writeSQL("insert into `${DB_PREFIX}catalog_product_entity_media_gallery` (`attribute_id`, `entity_id`, `value`) select "+MAGENTO_MEDIA_ATTRIBUTE_ID+", entity_id, '" + p + "' from `${DB_PREFIX}catalog_product_entity` where sku='" + sku + "';\r\n");
				copyImageFile(productId, p, cp);
//				sb.append(p);
			}
			if (files.length > 1)
				writeSQL("\r\n");
//			return sb.toString();
		}
		return images;
	}

/*
	@SuppressWarnings("unused")
	private String getImageGallery(String productId, String sku, String image) throws IOException {
		if (image == null || !enableMultiImages || zencartPath == null)
			return null;
		File f = new File(zencartPath + "/images/" + image);
		if (!f.exists())
			return null;
		String fileName = f.getName();
		int dot = fileName.lastIndexOf('.');
		if (dot <= 0)
			return null;
		final String name = fileName.substring(0, dot).toLowerCase();
		final String ext = fileName.substring(dot).toLowerCase();
		if (ext.length() == 0)
			return null;
		final int len = fileName.length();
		String[] files = f.getParentFile().list(new FilenameFilter() {
			@Override
			public boolean accept(File dir, String fname) {
				if (fname.length() <= len)
					return false;
				String n = fname.toLowerCase();
				if (!(n.startsWith(name) && n.endsWith(ext)))
					return false;
				if (galleryPattern == null)
					return true;
				String subfix = n.substring(name.length(), n.length()-ext.length());
				return subfix.matches(galleryPattern);
			}
		});
		if (files != null && files.length > 0) {
			String path = image.substring(0, image.length() - fileName.length());
			StringBuilder sb = new StringBuilder();
			for (int i = 0; i < files.length; i++) {
				if (i > 0)
					sb.append(";");
				if (maxImages > 0 && i >= maxImages)
					break;
				String p = path + files[i];
				writeSQL("insert into `${DB_PREFIX}catalog_product_entity_media_gallery` (`attribute_id`, `entity_id`, `value`) select "+MAGENTO_GALLERY_ATTRIBUTE_ID+", entity_id, '" + p + "' from `${DB_PREFIX}catalog_product_entity` where sku='" + sku + "';\r\n");
				copyImageFile(productId, p);
				sb.append(p);
			}
			if (files.length > 1)
				writeSQL("\r\n");
			return sb.toString();
		}
		return null;
	}
*/
	
	private void writeRecords(List<String> categories, List<CustomOption> options, CustomOption configurable, String sku, List<String> images) throws IOException {
		int a = Utils.size(categories);
		int c = configurable == null ? 0 : Math.max(0, Utils.size(configurable.getItems()) - 1);
		int b = 0;
		int d = Utils.size(images);
		if (options != null) {
			for (CustomOption option : options) {
				b += Math.max(1, Utils.size(option.getItems()));
			}
			b --;
		}
		int t = Math.max(d, Math.max(a, Math.max(c, b)));
		for (int i = 0; i < t; i++) {
			blankFields(4);
			writeCategory(categories, i);
			blankFields(76);
			writeImage(images, i);
			if (hasConfigurable())
				blankFields(configurableAttributes.length);
//			if (enableMultiImages)
//				blankField();
			writeCustomOptions(options, i+1);
			if (hasConfigurable())
				writeConfigurableOptions(sku, configurable, i+1);
			newline();
		}
	}
	
	private void writeImage(List<String> images, int index) throws IOException {
		if (images == null || images.size() <= index) {
			blankFields(5);
			return;
		}
		writeFields(MAGENTO_MEDIA_ATTRIBUTE_ID, images.get(index), null, index + 2, 0);
	}
	
	private void writeCategory(List<String> categories, int index) throws IOException {
		if (categories != null && index < categories.size())
			writeFields(category(categories.get(index)));
		else
			blankField();
	}
	
	private void writeCustomOptions(List<CustomOption> options, int index) throws IOException {
		if (options == null || options.size() == 0) {
			blankFields(12);
			return;
		}
		int t = 0;
		for (int i = 0, j=options.size(); i<j; i++) {
			CustomOption option = options.get(i);
			if (t == index) {
				blankField();					//_custom_option_store
				writeFields(option.getType());	//_custom_option_type
				writeFields(option.getTitle());	//_custom_option_title
				writeFields(1);					//_custom_option_is_required
				blankField();					//_custom_option_price
				if (option.isSelect())			//_custom_option_sku
					blankField();				//
				else							//
					writeFields(option.getSku());//
				blankField();					//_custom_option_max_characters
				writeFields(i + 1);				//_custom_option_sort_order
				if (option.isSelect()) {
					CustomOptionItem[] items = option.getItems();
					if (items == null || items.length == 0) {
						blankFields(4);
					} else {
						CustomOptionItem item = items[0];
						writeFields(item.getTitle());	//_custom_option_row_title
						blankField();					//_custom_option_row_price
						writeFields(item.getSku());		//_custom_option_row_sku
						writeFields(1);					//_custom_option_row_sort
					}
				} else {
					blankFields(4);				//_custom_option_row_title	_custom_option_row_price	_custom_option_row_sku	_custom_option_row_sort
				}
				return;
			} else {
				int s = Math.max(Utils.size(option.getItems()), 1);
				if (t + s <= index) {
					t += s;
					continue;
				}
				blankFields(8);
				CustomOptionItem[] items = option.getItems();
				int n = index - t;
				CustomOptionItem item = items[n];
				writeFields(item.getTitle());	//_custom_option_row_title
				blankField();					//_custom_option_row_price
				writeFields(item.getSku());		//_custom_option_row_sku
				writeFields(n + 1);				//_custom_option_row_sort
				return;
			}
		}
		blankFields(12);
	}
	
	private void writeConfigurableOptions(String sku, CustomOption option, int index) throws IOException {
		if (option == null) {
			blankFields(4);
			return;
		}
		if (option.isSelect()) {
			CustomOptionItem[] items = option.getItems();
			if (index >= items.length) {
				blankFields(4);
				return;
			}
			sku = combineSku(sku, items[index].getSku());
			writeFields(sku);
			if (index == 0) {
				writeFields(attributeCode(option.getTitle()));
			} else {
				blankField();
			}
		} else {
			if (index == 0) {
				sku = combineSku(sku, option.getSku());
				writeFields(sku);
				writeFields(attributeCode(option.getTitle()));
			} else {
				blankFields(2);
			}
		}
		blankFields(2);
	}
	
	private void writeConfigurableFields(CustomOption configurableAttribute, int index) throws IOException {
		if (hasConfigurable()) {
			if (configurableAttributes == null) {
				CustomOptionItem[] items = configurableAttribute == null ? null : configurableAttribute.getItems();
				if (index < Utils.size(items)) {
					writeFields((items[index].getTitle()));		//Migrate.code
				} else {
					blankField();
				}
				return;
			}
			for (int i = 0; i < configurableAttributes.length; i++) {
				boolean f = false;
				if (configurableAttribute != null && configurableAttributes[i].equals(configurableAttribute.getTitle())) {
					CustomOptionItem[] items = configurableAttribute.getItems();
					if (index < items.length) {
						writeFields((items[index].getTitle()));	//Migrate.code
						f = true;
					}
				}
				if (!f)
					blankField();
			}
		}
	}
	
/*	private CustomOption getConfigurable(CustomOption[] options, CustomOption[] productOptions) {
		if (options != null)
			for (int i = 0; i < options.length; i++) {
				if (options[i].isConfigure()) {
					return options[i];
				}
			}
		if (productOptions != null && productOptions.length > 0)
			for (int i = 0; i < productOptions.length; i++) {
				if (productOptions[i].setConfigurable(configurableAttributes)) {
					return productOptions[i];
				}
			}
		return null;
	}
*/	
	
/*	private void writeOptions(CustomOption[] options, int first) throws IOException  {
		if (options == null)
			return;
		for (int i = 0; i < options.length; i++) {
			if (first != 0 || i != 0)
				blankFields(81);
			CustomOption option = options[i];
			blankField();						//custom_option_store
			writeFields(option.getType());		//custom_option_type
			writeFields(option.getTitle());		//custom_option_title
			writeFields(option.isRequire());	//custom_option_is_required
			blankField();						//custom_option_price
			writeFields(option.getSku());		//custom_option_sku
			blankField();						//custom_option_max_characters(field, area)
			writeFields(first+i+1);				//custom_option_sort_order
			if (!option.isSelect()) {
				blankFields(4);					//custom_option_row_title,custom_option_row_price,custom_option_row_sku,custom_option_row_sort
			} else {
				CustomOptionItem[] items = option.getItems();
				for (int j = 0; j < items.length; j++) {
					if (j != 0)
						blankFields(89);
					CustomOptionItem item = items[j];
					writeFields(item.getTitle());	//custom_option_row_title
					writeFields(item.getPrice());	//custom_option_row_price
					writeFields(item.getSku());		//custom_option_row_sku
					writeFields(j+1);				//custom_option_row_sort
				}
			}
		}
	}
*/	
	@SuppressWarnings("unchecked")
	private List<String> getCategoryIds(String productId) {
		List<String> r = new ArrayList<String>();
		String sql = "select categories_id from `${DB_PREFIX}products_to_categories` where products_id=" + productId;
		List<Object[]> o = (List<Object[]>)executeSql(false, sql);		//hibernate 结果为List<Integer>
		LinkedHashSet<String> categories = new LinkedHashSet<String>();
		for (Object[] os : o) {
			String cid = v(os[0]);
			if (categories.contains(cid))
				continue;
			categories.add(cid);
			if (inherit) {
				cid = categoryParents.get(cid);
				while (cid != null && !categories.contains(cid)) {
					categories.add(cid);
					cid = categoryParents.get(cid);
				}
			}
		}
		r.addAll(categories);
		return r;
	}
	
	@SuppressWarnings("unchecked")
	private CustomOption readOption(String optionId) {
		String sql = 
		"select " +
		"	o.products_options_name,ot.products_options_types_name,ov.products_options_values_name\r\n"+
		"from `${DB_PREFIX}products_options` o\r\n"+
		"	left join `${DB_PREFIX}products_options_types` ot on o.products_options_type=ot.products_options_types_id\r\n"+
		"	left join `${DB_PREFIX}products_options_values_to_products_options` ovto on o.products_options_id=ovto.products_options_id\r\n"+
		"	left join `${DB_PREFIX}products_options_values` ov on ovto.products_options_values_id=ov.products_options_values_id\r\n"+
		"where \r\n" +
		"	o.products_options_id="+optionId+"\r\n"+
		"order by \r\n" +
		"	ov.products_options_values_sort_order";
		List<Object[]> o = (List<Object[]>)executeSql(false, sql);
		CustomOption option = null;
		for (Object[] record : o) {
			if (option == null) {
				option = new CustomOption();
				option.setTitle(v(record[0]));
				option.setZencartType(v(record[1]));
				option.setRequire(true);
				if (option.isSelect()) {
					CustomOptionItem item = new CustomOptionItem();
					item.setTitleSku(v(record[2]));
					option.addOptionItem(item);
				} else {
					option.sku(option.getTitle());
					option.setValue(v(record[2]));
				}
			} else {
				CustomOptionItem item = new CustomOptionItem();
				item.setTitleSku(v(record[2]));
				option.addOptionItem(item);
			}
		}
		return option;
	}
	
	@SuppressWarnings("unchecked")
	private void readProductOptionIds(String productId) {
		String sql = 
			"select distinct options_id,products_id from `${DB_PREFIX}products_attributes`  where products_id="+productId+" order by options_id";
		List<Object[]> o = (List<Object[]>)executeSql(false, sql);
		for (Object[] record : o) {
			optionIds.add(v(record[0]));
		}
	}

	@SuppressWarnings("unchecked")
	private List<CustomOption> getOptions(String productId) {
		String sql = 
		"select " +
		"	a.products_id,ot.products_options_types_name,o.products_options_id,o.products_options_name,ov.products_options_values_name,a.products_options_sort_order " +
		"from `${DB_PREFIX}products_attributes` a \r\n"+
		"	left join `${DB_PREFIX}products_options` o on a.options_id=o.products_options_id\r\n"+
		"	left join `${DB_PREFIX}products_options_values` ov on a.options_values_id=ov.products_options_values_id\r\n"+
		"	left join `${DB_PREFIX}products_options_types` ot on o.products_options_type=ot.products_options_types_id\r\n"+
		"where \r\n" +
		"	a.products_id="+productId+"\r\n"+
		"order by \r\n" +
		"	o.products_options_id,a.products_options_sort_order,ov.products_options_values_name\r\n";
		List<Object[]> o = (List<Object[]>)executeSql(false, sql);
		List<CustomOption> options = new ArrayList<CustomOption>();
		CustomOption option = null;
		String lastOptionId = null;
		for (Object[] record : o) {
			String optionId = v(record[2]);
			String optionName = v(record[3]);
			if (in(optionName, ignoredProductAttributes))
				continue;
//			if (in(optionName, configurableAttributes))
//				continue;
			if (optionId == null) {
//				System.out.println(sql);
				continue;
			}
			if (optionId.equals(lastOptionId)) {
				CustomOptionItem item = new CustomOptionItem();
				String title = v(record[4]);
				item.setTitle(title);
				item.sku(handlers.handle(FIELD_ATTRIBUTE_ITEM_SKU, title));
				option.addOptionItem(item);
			} else {
				option = new CustomOption();
				option.setTitle(v(record[3]));
				option.setZencartType(v(record[1]));
				option.setRequire(true);
				if (option.isSelect()) {
					CustomOptionItem item = new CustomOptionItem();
					String title = v(record[4]);
					item.setTitle(title);
					item.sku(handlers.handle(FIELD_ATTRIBUTE_ITEM_SKU, title));
//					item.setTitleSku(v(record[4]));
					option.addOptionItem(item);
				} else {
					option.sku(handlers.handle(FIELD_ATTRIBUTE_SKU, option.getTitle()));
				}
				options.add(option);
				lastOptionId = optionId;
			}
		}
		return options;
	}
	
	/** Block after Info Column/Product Info Column */
	private String options_container() {
		return "Block after Info Column";
	}
	
	private String type(boolean configurable) {
		return configurable ? "configurable" : "simple";
	}
	
	private String image(Object field) {
		if (field == null)
			return "no_selection";
		String image = v(field);
		if (!image.startsWith("/"))
			return "/" + v(field);
		return image;
	}
	
	private String category(Object field) {
		if (field == null)
			return null;
		String categoryId = v(field);
		if (categoryId.equals(UNCATEGORIZED_ID))
			return categoryPrefix + UNCATEGORIZED_NAME;
		if (StringUtils.isNumeric(categoryId)) {
			String[] ts = getCategoryTree(categoryId);
			if (ts != null && ts.length > 0)
				return ts[0];
		}
		return categoryId;
	}
	private String[] category2(Object field) {
		if (field == null)
			return null;
		String categoryId = v(field);
		if (categoryId.equals(UNCATEGORIZED_ID))
			return new String[] {categoryPrefix + UNCATEGORIZED_NAME};
		if (StringUtils.isNumeric(categoryId))
			return getCategoryTree(categoryId);
		return new String[] {categoryId};
	}
	
	private String urlkey(Object field) {
		if (field == null || !(field instanceof String)) {
			return null;
		}
		String key = (String)field;
		key = key.toLowerCase();
		key = key.replaceAll("[^\\w]+", " ");
		key = key.trim().replace(' ', '_');
		return key;
	}
	
	private void newline() throws IOException {
		csvOut.write(NEWLINE);
	}
	private void blankField() throws IOException {
		blankFields(1);
	}
	private void blankFields(int count) throws IOException {
		for (int i = 0; i< count; i++)
			writeFields((Object)null);
	}
	
	private void writeFields(Object... fields) throws IOException {
		for (int i = 0; i < fields.length; i++) {
			Object field = fields[i];
			if (field != null) {
				if (field instanceof Integer || field instanceof Long || field instanceof Short || field instanceof Float || field instanceof BigDecimal) {
					csvOut.write(field.toString());
				} else if (field instanceof String) {
					String s = (String) field;
					if (StringUtils.containsNone(s, "\" \t\r\n")) {
						csvOut.write(s);
					} else {
						s = StringUtils.replace(s, "\"", "\"\"");
						csvOut.write("\"" + s + "\"");
					}
				} else if (field instanceof Date) {
					String s = DateUtils.toString((Date)field, DATE_FORMAT);
					csvOut.write("\"" + s + "\"");
				} else if (field instanceof Boolean) {
					Boolean v = (Boolean)field;
					csvOut.write(v ? "1" : "0");
				} else {
					System.out.println(field.getClass());
				}
			} else {
				//do nothing
			}
			csvOut.write(",");
		}
	}
	
	private void copyImageFile(String productId, String srcFile, String destFile) throws IOException {
		String mpath = magentoPath;
		if (mpath == null)
			mpath = outputPath;
		if (zencartPath == null || mpath == null)
			return;
		if (srcFile == null || "no_selection".equals(srcFile))
			return;
		File src = new File(zencartPath + "/images/" + srcFile);
		if (!src.exists()) {
			System.out.println("\t" + productId + "\t" + srcFile + " not found");
			addUpdateSql("update `{$1}products` set products_status=0 where products_id={$2};", zencartPrefix, productId);
			return;
		}
		File dest = new File(mpath + (fixImageGalleryError ? "/media/catalog/product/" : "/media/import/") + destFile);
		if (dest.exists()) {
			System.out.println(productId + "\t" + srcFile + " exists");
			addUpdateSql("update `{$1}products` set products_status=0 where products_id={$2};", zencartPrefix, productId);
			return;
		}
		if (!dest.getParentFile().exists())
			if (!dest.getParentFile().mkdirs())
				return;
		if (moveImageFile)
			src.renameTo(dest);
		else
			FileUtils.copyFile(src, dest, false);
//		System.out.println(productId + "\t" + file);
	}
	
	/**
	 * 设置产品所属网店
	 * */
	public void setWebsite(String website) {
		this.website = website;
	}
	
	/**
	 * 设置产品属性集, 可配置属性
	 * @param attributeSet 属性集, 值为空或者'Default'时下面两个属性失效, 所有产品为不可配置产品
	 * @param enableConfigurable 是否启用可配置选项, 为否时下面的选项失效
	 * @param configurableAttributes 所有可配置属性
	 */
	public void setAttributeSet(String attributeSet, boolean enableConfigurable, String[] configurableAttributes) {
		if (StringUtils.isEmpty(attributeSet)) {
			this.attributeSet = DEFAULT_ATTRIBUTE_SET;
			this.enableConfigurable = false;
			this.configurableAttributes = null;
		} else {
			this.attributeSet = attributeSet;
			this.enableConfigurable = enableConfigurable;
			this.configurableAttributes = enableConfigurable ? configurableAttributes : null;
		}
	}
	
	/**
	 * 指定自定义选项和可配置属性
	 * @param options 自定义选项
	 * @param configurableAttribute 可配置属性
	 */
	public void setOptions(CustomOption[] options, CustomOption configurableAttribute) {
		this.options = options;
		this.configurableAttribute = configurableAttribute;
	}
	
	/**
	 * 设置忽略的产品属性
	 * @param ignoreAll 忽略所有产品属性
	 * @param ignoredAttributes 指定忽略产品属性名称
	 */
	public void setIgnoredAttributes(boolean ignoreAll, String[] ignoredAttributes) {
		this.ignoreAllProductAttributes = ignoreAll;
		this.ignoredProductAttributes = ignoredAttributes;
	}
	
	/**
	 * @param enableMultiImages 是否启用多图
	 * @param zencartPath ZenCart目录, 用于复制产品图片
	 * @param magentoPath Magento目录, 用于复制产品图片
	 * @param maxImages 限制附加图的数量
	 */
	public void setProductImagePath(boolean enableMultiImages, String zencartPath, String magentoPath, int maxImages) {
		this.enableMultiImages = enableMultiImages;
		this.zencartPath = enableMultiImages ? zencartPath : null;
		this.magentoPath = enableMultiImages ? magentoPath : null;
		this.maxImages = enableMultiImages ? maxImages : 0;
	}
	
	private static String v(Object o) {
		if (o == null)
			return null;
		return String.valueOf(o);
	}
	
	private String getMigrateName(String prefix) {
		return prefix + " " + DateUtils.toString(new Date(), "MM-dd-HH-mm");
	}
	
/*	private boolean ignoredAttribute(String attr) {
		if (ignoreAllProductAttributes)
			return true;
		if (ignoredProductAttributes == null)
			return false;
		for (int i = 0; i < ignoredProductAttributes.length; i++) {
			String a = ignoredProductAttributes[i];
			if (a.equals(attr))
				return true;
		}
		return false;
	}
	
	private boolean isConfigurable(String attr) {
		if (configurableAttributes == null || configurableAttributes.length == 0)
			return false;
		for (int i = 0; i < configurableAttributes.length; i++) {
			String s = configurableAttributes[i];
			if (s.equals(attr))
				return true;
		}
		return false;
	}
*/	
	@SuppressWarnings("unchecked")
	private void readZencartCategories() {
		if (categoryNames != null && categoryParents != null)
			return;
		String sql = 
			"select \r\n"+
			"	c.categories_id, c.parent_id, cd.categories_name\r\n" +
			"from `${DB_PREFIX}categories` c \r\n"+
			"	left join `${DB_PREFIX}categories_description` cd on c.categories_id=cd.categories_id and cd.language_id=${LANGUAGE}\r\n"+
			"";
		List<Object[]> o = (List<Object[]>)executeSql(false, sql);
		Map<String, String> parents = new HashMap<String, String>();
		for (Object[] objects : o) {
			String p = v(objects[1]);
			if (p == null || p.isEmpty() || "0".equals(p))
				continue;
			parents.put(v(objects[0]), p);
		}
		Map<String, String> names = new HashMap<String, String>();
		for (Object[] objects : o) {
			names.put(v(objects[0]), v(objects[2]));
		}
		categoryNames = names;
		categoryParents = parents;
	}
	
	/**
	 * 设置产品分类过滤
	 * @param subcatalog 是否包含子分类
	 * @param categoryIds 分类IDs, 为null表示不过滤分类
	 * 查询分类SQL: select c.categories_id, cd.categories_name from  categories c left join categories_description cd on c.categories_id=cd.categories_id and cd.language_id=1 order by c.categories_id
	 * */
	public void setFilterCategories(boolean subcatalog, boolean uncategorized, String[] categoryIds) {
		if (categoryIds == null || categoryIds.length == 0 && !uncategorized) {
			filterCategoryIds = null;
			return;
		}
		filterCategoryIds = new HashSet<String>();
		notIncludeCategories = new LinkedHashSet<String>();
		readZencartCategories();
		if (subcatalog) {
			for (int i = 0; i < categoryIds.length; i++) {
				String categoryId = categoryIds[i];
				addCategory(categoryParents, filterCategoryIds, categoryId);
			}
		} else {
			for (int i = 0; i < categoryIds.length; i++) {
				filterCategoryIds.add(categoryIds[i]);
			}
		}
//		if (uncategorized) {
//			filterCategoryIds.add(UNCATEGORIZED_ID);
//			categoryNames.put(UNCATEGORIZED_ID, UNCATEGORIZED_NAME);
//		}
	}
	/**
	 * 
	 * @param subcatalog 是否包含子分类
	 * @param uncategorized 是否包含无分类产品
	 * @param categoryNames 为null表示不过滤分类
	 */
	public void setFilterCategoryNames(boolean subcatalog, boolean uncategorized, String[] categoryNames) {
		if (categoryNames == null || categoryNames.length == 0 && !uncategorized) {
			filterCategoryIds = null;
			return;
		}
		filterCategoryIds = new HashSet<String>();
		notIncludeCategories = new LinkedHashSet<String>();
		readZencartCategories();
		if (subcatalog) {
			for (int i = 0; i < categoryNames.length; i++) {
				String categoryName = categoryNames[i];
				addCategory(categoryParents, filterCategoryIds, getCategoryId(categoryName));
			}
		} else {
			for (int i = 0; i < categoryNames.length; i++) {
				filterCategoryIds.add(getCategoryId(categoryNames[i]));
			}
		}
//		if (uncategorized) {
//			filterCategoryIds.add(UNCATEGORIZED_ID);
//			this.categoryNames.put(UNCATEGORIZED_ID, UNCATEGORIZED_NAME);
//		}
	}
	private String getCategoryId(String categoryName) {
		if (categoryNames == null)
			return null;
		for (Map.Entry<String, String> entry : categoryNames.entrySet()) {
			if (categoryName.equals(entry.getValue()))
				return entry.getKey();
		}
		return null;
	}
	
	/** 指定导入的产品类别  
	 * @param ignoreProductCategories 忽略所有产品本身的类别
	 * @param categories 指定的新类别名称路径
	 * */
	public void setProductCategories(boolean ignoreProductCategories, String... categories) {
		this.ignoreProductCategories = ignoreProductCategories;
		this.productCategories = categories;
	}
	
	/** 设置新产品时间段 */
	public void setProductAsNewDate(String start, String end) {
		this.averageNew = false;
		this.newFromDate = start;
		this.newToDate = end;
	}
	public void setProductAsNewDate(boolean averageNew, String end) {
		this.averageNew = averageNew;
		this.newToDate = end;
	}
	
	public void setLimit(int first, int limit) {
		this.first = first;
		this.limit = limit;
	}
	
	public int getFirst() {
		return first;
	}
	
	/**
	 * 设置输出cvs, sql等文件路径
	 * */
	public void setOutputPath(String path) {
		File f = new File(path);
		if (!f.isDirectory() && !f.mkdirs()) {
			throw new RuntimeException("invalid output path: " + path);
		}
		this.outputPath = path + File.separator;
	}
	
	/**
	 * 设置表前缀
	 * @param zencartPrefix Zencart表前缀, 默认为空
	 * @param magentoPrefix Magento表前缀, 默认为ues_
	 */
	public void setTablePrefix(String zencartPrefix, String magentoPrefix) {
		this.zencartPrefix = StringUtils.defaultString(zencartPrefix);
		this.magentoPrefix = StringUtils.defaultString(magentoPrefix);
	}
	
	private void addCategory(Map<String, String> all, Set<String> current, String categoryId) {
		if (current.contains(categoryId))
			return;
		current.add(categoryId);
		for (Map.Entry<String, String> entry : all.entrySet()) {
			if (categoryId.equals(entry.getValue())) {
				addCategory(all, current, entry.getKey());
				break;
			}
		}
	}
	
	private static boolean in(String search, String[] strs) {
		if (strs == null || search == null)
			return false;
		for (int i = 0; i < strs.length; i++) {
			if (search.equals(strs[i]))
				return true;
		}
		return false;
	}
	
	/**
	 * 显示当前产品属性
	 * */
	@SuppressWarnings("unchecked")
	public void showOptions() {
		String sql = "select products_options_id, products_options_name from `${DB_PREFIX}products_options` where language_id=${LANGUAGE}";
		List<Object[]> o = (List<Object[]>)executeSql(false, sql);
		System.out.println();
		for (Object[] record : o) {
			System.out.println(record[0] + "\t" + record[1]);
		}
		System.out.println();
	}
	
	/**
	 * 显示所有分类
	 * */
	public void showCategories() {
		readZencartCategories();
		List<String> categories = new ArrayList<String>();
		categories.addAll(categoryParents.keySet());
/*		Collections.sort(categories, new Comparator<String>() {
			@Override
			public int compare(String o1, String o2) {
				
				return 0;
			}
		});
*/
		System.out.println();
		for (String category : categories) {
			System.out.println(category + "\t" + categoryNames.get(category));
		}
		System.out.println();
	}
	
	private String attributeCode(String title) {
		return "ues_" + MigrateUtils.code(title);
	}
	
	private String combineSku(String parentSku, String sku) {
		return parentSku + "_ues_" + sku;
	}
	
	public void setManufacturer(String manufacturer) {
		this.manufacturer = manufacturer;
	}
	
	public void setHandlers(FieldHandlers handlers) {
		this.handlers = handlers;
	}
	
	public void setCategoryPrefix(String categoryPrefix) {
		if (categoryPrefix == null)
			categoryPrefix = "";
		if (categoryPrefix.length() > 0 && !categoryPrefix.endsWith("/"))
			categoryPrefix += "/";
		this.categoryPrefix = categoryPrefix;
	}
	
	private String[] getImages(String image) {
		if(!fixedMultiImages || image.indexOf(',') == -1)
			return new String[] {image};
		return image.split(",");
	}
	
	/**
	 * 
	 * @param field 字段
	 * @param str 原值
	 * @param productName 产品名称
	 * @param productSku 产品SKU
	 * @param extra 产品类别, 或其它的附加属性
	 */
	public String handle(String field, String str, String productName, String productSku, Object extra) {
		if (this.handlers == null)
			return str;
		return handlers.handle(field, str, productName, productSku, extra);
	}
	public String[] handleEx(String field, String str, String productName, String productSku, String extra) {
		if (this.handlers == null)
			return new String[] {str};
		return handlers.handleEx(field, str, productName, productSku, extra);
	}
	
	public String handle(String field, String str) {
		if (this.handlers == null)
			return str;
		return handlers.handle(field, str);
	}
	
	public void setExtraAttributes(String... attrs) {
		this.extraAttributes = attrs;
	}
	
	public void setFixedMultiImages(boolean fixedMultiImages) {
		this.fixedMultiImages = fixedMultiImages;
	}
}
